﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Model;
using System.Collections.Generic;
namespace DAL
{
    /// <summary>
    /// 数据访问类:PMS_ProjectRenewal
    /// </summary>
    public class PMS_ProjectRenewalDAL
    {
        public PMS_ProjectRenewalDAL()
        { }

        /// <summary>
        /// 获得单条数据
        /// </summary>
        public PMS_ProjectRenewal GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.*,b.city FROM PMS_ProjectRenewal as a left join PMS_Project as b on a.projectID=b.id where a.isDelete='0' and a.id=@id");
            SqlParameter[] values = { new SqlParameter("@id", id) };
            return SqlHelper.GetDataByColumns<PMS_ProjectRenewal>(Comm.Config.PMS, strSql.ToString(), values);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<PMS_ProjectRenewal> GetList(string projectID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.*,b.city FROM PMS_ProjectRenewal as a left join PMS_Project as b on a.projectID=b.id where a.isDelete='0' and a.projectID=@projectID order by a.createDate desc ");
            SqlParameter[] parameters = { new SqlParameter("@projectID", projectID) };
            return SqlHelper.GetDataByColumnsLst<PMS_ProjectRenewal>(Comm.Config.PMS, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<PMS_ProjectRenewal> GetList(PMS_ProjectRenewalQuery query)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.*,b.city FROM PMS_ProjectRenewal as a left join PMS_Project as b on a.projectID=b.id where a.isDelete='0' and a.projectID=@projectID ");
            if (query.startDate != null && query.startDate != DateTime.MinValue)
            {
                strSql.Append(" and a.signingDate>='" + query.startDate + "' ");
            }
            if (query.endDate != null && query.endDate != DateTime.MinValue)
            {
                strSql.Append(" and a.signingDate<='" + query.endDate + "' ");
            }
            strSql.Append(" order by a.createDate desc");
            SqlParameter[] parameters = { new SqlParameter("@projectID", query.projectID) };
            return SqlHelper.GetDataByColumnsLst<PMS_ProjectRenewal>(Comm.Config.PMS, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetListTable(PMS_ProjectRenewalQuery query)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select projectIDS=b.projectID,b.projectName,b.city,a.* FROM PMS_ProjectRenewal as a left join PMS_Project as b on a.projectID=b.id where a.isDelete='0' and a.projectID=@projectID ");
            if (query.startDate != null && query.startDate != DateTime.MinValue)
            {
                strSql.Append(" and a.signingDate>='" + query.startDate + "' ");
            }
            if (query.endDate != null && query.endDate != DateTime.MinValue)
            {
                strSql.Append(" and a.signingDate<='" + query.endDate + "' ");
            }
            strSql.Append(" order by a.createDate desc");
            SqlParameter[] parameters = { new SqlParameter("@projectID", query.projectID) };
            return SqlHelper.ExecuteGetTable(Comm.Config.PMS, CommandType.Text, strSql.ToString(), parameters);
        }

        #region  Method

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.PMS_ProjectRenewal model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@projectID", SqlDbType.NVarChar,50),
					new SqlParameter("@customerName", SqlDbType.NVarChar,50),
					new SqlParameter("@repairsDate", SqlDbType.DateTime),
					new SqlParameter("@repairsPeople", SqlDbType.NVarChar,50),
					new SqlParameter("@repairsTel", SqlDbType.NVarChar,50),
					new SqlParameter("@bugDescription", SqlDbType.NVarChar),
					new SqlParameter("@maintainDate", SqlDbType.DateTime),
					new SqlParameter("@jobID", SqlDbType.NVarChar,50),
					new SqlParameter("@bugType", SqlDbType.NVarChar,50),
					new SqlParameter("@device", SqlDbType.NVarChar,100),
					new SqlParameter("@deviceType", SqlDbType.NVarChar,100),
					new SqlParameter("@maintainDescription", SqlDbType.NVarChar),
					new SqlParameter("@maintainPeople", SqlDbType.NVarChar,50),
					new SqlParameter("@status", SqlDbType.NVarChar,50),
					new SqlParameter("@completeDate", SqlDbType.DateTime)};
            parameters[0].Value = model.projectID;
            parameters[1].Value = string.IsNullOrEmpty(model.customerName) ? "" : model.customerName;
            if (model.repairsDate == null || model.repairsDate == DateTime.MinValue)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = model.repairsDate;
            }
            parameters[3].Value = string.IsNullOrEmpty(model.repairsPeople) ? "" : model.repairsPeople;
            parameters[4].Value = string.IsNullOrEmpty(model.repairsTel) ? "" : model.repairsTel;
            parameters[5].Value = string.IsNullOrEmpty(model.bugDescription) ? "" : model.bugDescription;
            if (model.maintainDate == null || model.maintainDate == DateTime.MinValue)
            {
                parameters[6].Value = DBNull.Value;
            }
            else
            {
                parameters[6].Value = model.maintainDate;
            }
            parameters[7].Value = string.IsNullOrEmpty(model.jobID) ? "" : model.jobID;
            parameters[8].Value = string.IsNullOrEmpty(model.bugType) ? "" : model.bugType;
            parameters[9].Value = string.IsNullOrEmpty(model.device) ? "" : model.device;
            parameters[10].Value = string.IsNullOrEmpty(model.deviceType) ? "" : model.deviceType;
            parameters[11].Value = string.IsNullOrEmpty(model.maintainDescription) ? "" : model.maintainDescription;
            parameters[12].Value = string.IsNullOrEmpty(model.maintainPeople) ? "" : model.maintainPeople;
            parameters[13].Value = model.status;
            if (model.completeDate == null || model.completeDate == DateTime.MinValue)
            {
                parameters[14].Value = DBNull.Value;
            }
            else
            {
                parameters[14].Value = model.completeDate;
            }

            int row = SqlHelper.ExecuteNonQuery(Comm.Config.PMS, CommandType.StoredProcedure, "sp_AddProjectRenewal", parameters);
            if (row > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.PMS_ProjectRenewal model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PMS_ProjectRenewal set ");
            strSql.Append("customerName=@customerName,");
            strSql.Append("repairsDate=@repairsDate,");
            strSql.Append("repairsPeople=@repairsPeople,");
            strSql.Append("repairsTel=@repairsTel,");
            strSql.Append("bugDescription=@bugDescription,");
            strSql.Append("maintainDate=@maintainDate,");
            strSql.Append("jobID=@jobID,");
            strSql.Append("bugType=@bugType,");
            strSql.Append("device=@device,");
            strSql.Append("deviceType=@deviceType,");
            strSql.Append("maintainDescription=@maintainDescription,");
            strSql.Append("maintainPeople=@maintainPeople,");
            strSql.Append("status=@status,");
            strSql.Append("completeDate=@completeDate");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int),
					new SqlParameter("@customerName", SqlDbType.NVarChar,50),
					new SqlParameter("@repairsDate", SqlDbType.DateTime),
					new SqlParameter("@repairsPeople", SqlDbType.NVarChar,50),
					new SqlParameter("@repairsTel", SqlDbType.NVarChar,50),
					new SqlParameter("@bugDescription", SqlDbType.NVarChar),
					new SqlParameter("@maintainDate", SqlDbType.DateTime),
					new SqlParameter("@jobID", SqlDbType.NVarChar,50),
					new SqlParameter("@bugType", SqlDbType.NVarChar,50),
					new SqlParameter("@device", SqlDbType.NVarChar,100),
					new SqlParameter("@deviceType", SqlDbType.NVarChar,100),
					new SqlParameter("@maintainDescription", SqlDbType.NVarChar),
					new SqlParameter("@maintainPeople", SqlDbType.NVarChar,50),
					new SqlParameter("@status", SqlDbType.NVarChar,50),
					new SqlParameter("@completeDate", SqlDbType.DateTime)};
            parameters[0].Value = model.id;
            parameters[1].Value = string.IsNullOrEmpty(model.customerName) ? "" : model.customerName;
            if (model.repairsDate == null || model.repairsDate == DateTime.MinValue)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = model.repairsDate;
            }
            parameters[3].Value = string.IsNullOrEmpty(model.repairsPeople) ? "" : model.repairsPeople;
            parameters[4].Value = string.IsNullOrEmpty(model.repairsTel) ? "" : model.repairsTel;
            parameters[5].Value = string.IsNullOrEmpty(model.bugDescription) ? "" : model.bugDescription;
            if (model.maintainDate == null || model.maintainDate == DateTime.MinValue)
            {
                parameters[6].Value = DBNull.Value;
            }
            else
            {
                parameters[6].Value = model.maintainDate;
            }
            parameters[7].Value = string.IsNullOrEmpty(model.jobID) ? "" : model.jobID;
            parameters[8].Value = string.IsNullOrEmpty(model.bugType) ? "" : model.bugType;
            parameters[9].Value = string.IsNullOrEmpty(model.device) ? "" : model.device;
            parameters[10].Value = string.IsNullOrEmpty(model.deviceType) ? "" : model.deviceType;
            parameters[11].Value = string.IsNullOrEmpty(model.maintainDescription) ? "" : model.maintainDescription;
            parameters[12].Value = string.IsNullOrEmpty(model.maintainPeople) ? "" : model.maintainPeople;
            parameters[13].Value = model.status;
            if (model.completeDate == null || model.completeDate == DateTime.MinValue)
            {
                parameters[14].Value = DBNull.Value;
            }
            else
            {
                parameters[14].Value = model.completeDate;
            }

            int rows = SqlHelper.ExecuteNonQuery(Comm.Config.PMS, CommandType.Text, strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PMS_ProjectRenewal set isDelete='1' ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = { new SqlParameter("@id", id) };

            int rows = SqlHelper.ExecuteNonQuery(Comm.Config.PMS, CommandType.Text, strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion  Method
    }
}

